package com.jiangtour.repository;

import com.jiangtour.dataobject.SiteInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface SiteInfoRepository  extends JpaRepository<SiteInfo, String>,JpaSpecificationExecutor<SiteInfo> {

    //SELECT*,ROUND(6378.138*2*ASIN(SQRT(POW(SIN((30.536526*PI()/180-lat*PI()/180)/2),2)+COS(30.536526*PI()/180)*COS(lat*PI()/180)*POW(SIN((104.263489*PI()/180-lng*PI()/180)/2),2)))*1000) AS juli FROM site_info ORDER BY juli ASC
    //@Query(value = "select u.* from appuser_te u LEFT JOIN app_user_history his on his.user_id=u.id where his.apple_app_id=:appleAppId limit 1,3",nativeQuery = true)
    @Query(value = "SELECT*,ROUND(6378.138 *2*ASIN(SQRT(POW(SIN((:lat *PI()/180-lat*PI()/180)/2),2)+COS( :lat *PI()/180)*COS(lat*PI()/180)*POW(SIN((:lng *PI()/180-lng*PI()/180)/2),2)))*1000) AS juli FROM site_info ORDER BY juli ASC",nativeQuery = true)
    List<SiteInfo> findDistanceByLonAndLat(@Param(value="lng") double lng,@Param(value="lat") double lat);

    SiteInfo findByAccount(String accountId);

}
